/*
 * Copyright Notice ====================================================
 * This file contains proprietary information of JeeStrap Co.
 * Copying or reproduction without prior written approval is prohibited.
 * Copyright (c) 2014 All rights reserved. =============================
 */

package com.jeestrap.common.smartcoder.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.jeestrap.common.smartcoder.domain.DbTable;
import com.jeestrap.common.smartcoder.domain.OrmTableField;
import com.jeestrap.common.smartcoder.util.StrUtils;

public class DbUtil {

	private static final ThreadLocal<Connection> connectionTl = new ThreadLocal();

	public static Connection getDBConnection() {
		return connectionTl.get();
	}

	public static void realseConnection() {
		Connection con = connectionTl.get();
		connectionTl.remove();
		closeCon(con);
	}

	public static Connection getDBConnection(String dbName, String driver, String url, String user, String password)
		throws Exception {
		if (StrUtils.isNull(dbName)) {
			throw new Exception("请选择连接类型");
		}
		if (StrUtils.isNull(driver)) {
			throw new Exception("请填写驱动类名");
		}

		Connection con = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		}
		connectionTl.set(con);
		return con;
	}

	public static void closeCon(final Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException ex) {
				// ignore
			}
		}
	}

	public static void closeStmt(final Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException ex) {
				// ignore
			}
		}
	}

	public static void closeResultSet(final ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException ex) {
				// ignore
			}
		}
	}

	public static void close(ResultSet rs, Statement stmt, Connection conn) {
		closeResultSet(rs);
		closeStmt(stmt);
		closeCon(conn);
	}

	public static List<DbTable> getAllTables() {
		Connection con = getDBConnection();
		if (con == null) {
			return new ArrayList<DbTable>();
		}
		List<DbTable> localArrayList = new ArrayList<DbTable>();
		DatabaseMetaData meta;
		try {
			meta = con.getMetaData();
			String dbName = meta.getDatabaseProductName();
			ResultSet rs = meta.getTables(null, null, null, new String[]{"TABLE", "VIEW"});
			DbTable dbTable;
			while (rs.next()) {
				dbTable = new DbTable();
				dbTable.setTblName(rs.getString("TABLE_NAME"));
				if( dbName.toLowerCase().contains("mysql")){
					dbTable.setTblComments(getMySqlTblCommentByTableName(rs.getString("TABLE_NAME")));
				} else {
					dbTable.setTblComments(rs.getString("REMARKS"));
				}
				localArrayList.add(dbTable);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
		}
		return localArrayList;
	}

	public static String getMySqlTblCommentByTableName(String tableName) {
		String comment = null;
		try {
			Connection con = getDBConnection();
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName);
			if (rs != null && rs.next()) {
				String create = rs.getString(2);

				int index = create.indexOf("COMMENT='");
				if (index < 0) {
					return "";
				}
				comment = create.substring(index + 9);
				comment = comment.substring(0, comment.length() - 1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
		}
		return comment;
	}

	public static List<OrmTableField> getTableColumnsByTable(String paramString) throws SQLException {
		Connection con = getDBConnection();
		if (con == null) {
			return new ArrayList<OrmTableField>();
		}

		List primaryKeysList = getPrimaryKeys(con, paramString);
		List<OrmTableField> localArrayList = new ArrayList<OrmTableField>();
		DatabaseMetaData meta;
		try {

			String str1 = " select * from " + paramString + " where 1<>1";
			PreparedStatement stmt = con.prepareStatement(str1);
			ResultSet rs1 = stmt.executeQuery();
			ResultSetMetaData localResultSetMetaData = rs1.getMetaData();
			int intColumnCount = localResultSetMetaData.getColumnCount();

			OrmTableField dbColumn;
			for (int j = 1; j <= intColumnCount; j++) {
				dbColumn = new OrmTableField();
				dbColumn.setFieldName(localResultSetMetaData.getColumnName(j));
				dbColumn.setFieldType(localResultSetMetaData.getColumnTypeName(j));
				dbColumn.setFieldLabel(localResultSetMetaData.getColumnLabel(j));
				dbColumn.setJavaFieldName(getJavaFieldName(localResultSetMetaData.getColumnName(j)));
				dbColumn.setJavaFieldType(localResultSetMetaData.getColumnClassName(j));
				dbColumn.setMaxLength(new Integer(localResultSetMetaData.getColumnDisplaySize(j)));
				int k = localResultSetMetaData.isNullable(j);
				if (k == 0)
					dbColumn.setAllowNull(Boolean.FALSE);
				else if (k == 1)
					dbColumn.setAllowNull(Boolean.TRUE);
				else if (k == 2)
					dbColumn.setAllowNull(Boolean.TRUE);
				if ((primaryKeysList != null) && (primaryKeysList.contains(dbColumn.getFieldName().toUpperCase()))) {
					dbColumn.setPrimaryKey(Boolean.TRUE);
					dbColumn.setJspQueryField(Boolean.TRUE);
				}
				localArrayList.add(dbColumn);
			}

			meta = con.getMetaData();
			ResultSet rs2 = meta.getColumns(null, null, paramString, null);

			while (rs2.next()) {
				String colName = rs2.getString("COLUMN_NAME");
				String remarks = rs2.getString("REMARKS");
				if ((remarks != null) && (remarks.trim().length() > 0)) {
					Iterator<OrmTableField> localIterator = localArrayList.iterator();
					while (localIterator.hasNext()) {
						OrmTableField localTableField = localIterator.next();
						if (colName.equals(localTableField.getFieldName()))
							localTableField.setFieldLabel(remarks);
					}
				}
				// jdbc:mysql://localhost:3306/sgcrm
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
		}
		return localArrayList;
	}

	private static String getJavaFieldName(String paramString) {
		StringBuffer localStringBuffer = new StringBuffer();
		String str = paramString.trim().toLowerCase();
		String[] arrayOfString = str.split("_");
		for (int i = 0; i < arrayOfString.length; i++)
			if (i == 0)
				localStringBuffer.append(arrayOfString[i]);
			else
				localStringBuffer
					.append(arrayOfString[i].substring(0, 1).toUpperCase() + arrayOfString[i].substring(1));
		return localStringBuffer.toString();
	}

	private static List getPrimaryKeys(Connection paramConnection, String paramString) throws SQLException {
		ResultSet localResultSet = null;
		ArrayList localArrayList1 = new ArrayList();
		DatabaseMetaData localDatabaseMetaData = paramConnection.getMetaData();
		localResultSet = localDatabaseMetaData.getPrimaryKeys(null, null, paramString.toUpperCase());
		while (localResultSet.next())
			localArrayList1.add(localResultSet.getString("COLUMN_NAME").toUpperCase());
		ArrayList localArrayList2 = localArrayList1;
		return localArrayList2;
	}
}
